﻿using Sunboxsoft.Common;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sunboxsoft.ASMS.Server.Domain.Service
{
    public class QueryService
    {
        public ListResult<Entity.EMasterData> QueryMasterDataList()
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EMasterData> list = dbContext.Set<Entity.EMasterData>().Where(p => p.IsEnabled).ToList();
                return new ListResult<Entity.EMasterData>(list);
            }
        }

        public ListResult<Entity.EMasterDataItem> QueryMasterDataItemList(string masterDataCode)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EMasterDataItem> list = dbContext.Set<Entity.EMasterDataItem>().Where(p => p.MasterDataCode == masterDataCode).OrderBy(p=>p.SortIndex).ToList();
                return new ListResult<Entity.EMasterDataItem>(list);
            }
        }

        public PagedResult<Entity.EMasterDataItem> QueryMasterDataItemPagedList(string masterDataCode, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EMasterDataItem> list = dbContext.Set<Entity.EMasterDataItem>().Where(p => p.MasterDataCode == masterDataCode).OrderBy(p => p.SortIndex).Skip(start).Take(limit).ToList();
                int total = dbContext.Set<Entity.EMasterDataItem>().Where(p => p.MasterDataCode == masterDataCode).Count();
                return new PagedResult<Entity.EMasterDataItem>(start, limit, total, list);
            }
        }

        public ListResult<View.VStation> QueryStationListByStaff(Guid staffId)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VStation> list = dbContext.Database.SqlQuery<View.VStation>("exec [ASMS_SP_QueryStationListByStaff] @staffId={0}", staffId).ToList();

                return new ListResult<View.VStation>(list);
            }
        }

        public PagedResult<View.VCustomer> QueryCustomerPagedList(string filter, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_V_Customer ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_Customer ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (Name like {0} or Telephone like {0})");
                sqlTotalBuilder.Append(" (Name like {0} or Telephone like {0})");
                parameters.Add(string.Format("%{0}%", filter));
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                try
                {
                    List<View.VCustomer> list = dbContext.Database.SqlQuery<View.VCustomer>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                    int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                    return new PagedResult<View.VCustomer>(start, limit, total, list);
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTag, string.Format("QueryCustomerPagedList error,ex:{0}", ex));
                    return new PagedResult<View.VCustomer>(1, ex.Message);
                }
            }
        }

        public ListResult<View.VAutomobile> QueryAutomobileList(Guid customerId)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                try
                {
                    List<View.VAutomobile> list = dbContext.Set<View.VAutomobile>().Where(p => p.CustomerId == customerId).OrderBy(p => p.LPN).ToList();
                    return new ListResult<View.VAutomobile>(list);
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTag, string.Format("QueryAutomobileList error,ex:{0}", ex));
                    return new ListResult<View.VAutomobile>(1, ex.Message);
                }
            }
        }

        public PagedResult<View.VBookingBill> QueryBookingBillPagedList(Guid businessId, Guid stationId, DateTime? beginDate, DateTime? endDate, Guid? salesManagerId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by TargetDate asc) as _RowIndex, * from ASMS_V_BookingBill ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_BookingBill ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (isFirstCondition)
            {
                sqlBuilder.Append("  where");
                sqlTotalBuilder.Append(" where");
                isFirstCondition = false;
            }
            else
            {
                sqlBuilder.Append("  and");
                sqlTotalBuilder.Append(" and");
            }
            sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
            sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
            parameters.Add(businessId);
            if (isFirstCondition)
            {
                sqlBuilder.Append("  where");
                sqlTotalBuilder.Append(" where");
                isFirstCondition = false;
            }
            else
            {
                sqlBuilder.Append("  and");
                sqlTotalBuilder.Append(" and");
            }
            sqlBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
            sqlTotalBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
            parameters.Add(stationId);

            if (salesManagerId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" SalesManagerId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" SalesManagerId = {" + parameters.Count.ToString() + "}");
                parameters.Add(salesManagerId.Value);
            }

            if (beginDate != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" TargetDate >= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" TargetDate >= {" + parameters.Count.ToString() + "}");
                parameters.Add(beginDate.Value);
            }
            if (endDate != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" TargetDate <= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" TargetDate <= {" + parameters.Count.ToString() + "}");
                parameters.Add(endDate.Value);
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VBookingBill> list = dbContext.Database.SqlQuery<View.VBookingBill>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VBookingBill>(start, limit, total, list);
            }
        }

        public ListResult<View.VStaffPosition> QueryStaffPositionList(string filter, Guid businessId, Guid? stationId, string post)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                try
                {
                    if (string.IsNullOrEmpty(filter))
                    {
                        if (stationId == null)
                        {
                            List<View.VStaffPosition> list = dbContext.Set<View.VStaffPosition>().Where(p => p.BusinessId == businessId && p.Post == post).OrderBy(p => p.StaffName).ToList();
                            return new ListResult<View.VStaffPosition>(list);
                        }
                        else
                        {
                            List<View.VStaffPosition> list = dbContext.Set<View.VStaffPosition>().Where(p => p.BusinessId == businessId && p.Post == post && p.StationId == stationId.Value).OrderBy(p => p.StaffName).ToList();
                            return new ListResult<View.VStaffPosition>(list);
                        }
                    }
                    else
                    {
                        if (stationId == null)
                        {
                            List<View.VStaffPosition> list = dbContext.Set<View.VStaffPosition>().Where(p => p.BusinessId == businessId && p.Post == post && p.StaffName.Contains(filter)).OrderBy(p => p.StaffName).ToList();
                            return new ListResult<View.VStaffPosition>(list);
                        }
                        else
                        {
                            List<View.VStaffPosition> list = dbContext.Set<View.VStaffPosition>().Where(p => p.BusinessId == businessId && p.Post == post && p.StationId == stationId.Value && p.StaffName.Contains(filter)).OrderBy(p => p.StaffName).ToList();
                            return new ListResult<View.VStaffPosition>(list);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Logger.ErrorToTag(Library.LogTag, string.Format("QueryStaffPositionList error,ex:{0}", ex));
                    return new ListResult<View.VStaffPosition>(1, ex.Message);
                }
            }
        }

        /// <summary>
        /// 获取服务商里的员工，不考察岗位
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="businessId"></param>
        /// <param name="status"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public PagedResult<Entity.EStaff> QueryStaffPagedList(string filter, Guid businessId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name asc) as _RowIndex, * from ASMS_Staff ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_Staff ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (isFirstCondition)
            {
                sqlBuilder.Append("  where");
                sqlTotalBuilder.Append(" where");
                isFirstCondition = false;
            }
            else
            {
                sqlBuilder.Append("  and");
                sqlTotalBuilder.Append(" and");
            }
            sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
            sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
            parameters.Add(businessId);

            if (string.IsNullOrEmpty(filter) == false)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Name like {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Name like {" + parameters.Count.ToString() + "}");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EStaff> list = dbContext.Database.SqlQuery<Entity.EStaff>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<Entity.EStaff>(start, limit, total, list);
            }
        }

        public PagedResult<Entity.EStaff> QueryStaffPagedList(string filter, Guid businessId, Guid stationId, int status, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                SqlParameter pFilter = new SqlParameter("@filter", System.Data.SqlDbType.NVarChar, 64);
                if (!string.IsNullOrEmpty(filter))
                {
                    pFilter.Value = filter;
                }
                else
                {
                    pFilter.Value = DBNull.Value;
                }
                
                SqlParameter pBusinessId = new SqlParameter("@businessId", System.Data.SqlDbType.UniqueIdentifier);
                pBusinessId.Value = businessId;
                
                SqlParameter pStationId = new SqlParameter("@stationId", System.Data.SqlDbType.UniqueIdentifier);
                pStationId.Value = stationId;

                SqlParameter pStatus = new SqlParameter("@status", System.Data.SqlDbType.Int);
                pStatus.Value = status;

                SqlParameter pStart = new SqlParameter("@start", start);
                SqlParameter pLimit = new SqlParameter("@limit", limit);
                SqlParameter pTotal = new SqlParameter("@total", System.Data.SqlDbType.Int);
                pTotal.Direction = System.Data.ParameterDirection.Output;

                List<Entity.EStaff> list = dbContext.Database.SqlQuery<Entity.EStaff>("[ASMS_SP_QueryStaffPagedList] @filter,@businessId,@stationId,@status,@start,@limit,@total out", pFilter, pBusinessId, pStationId, pStatus, pStart, pLimit, pTotal).ToList();
                int total = Convert.ToInt32(pTotal.Value);
                return new PagedResult<Entity.EStaff>(start, limit, total, list);
            }
        }

        /// <summary>
        /// 获取管理员员工信息，不属于任何一个服务商
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="businessId"></param>
        /// <param name="stationId"></param>
        /// <param name="status"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public PagedResult<Entity.EStaff> QueryAdminStaffPagedList(string filter, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_Staff ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_Staff ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (isFirstCondition)
            {
                sqlBuilder.Append("  where");
                sqlTotalBuilder.Append(" where");
                isFirstCondition = false;
            }
            else
            {
                sqlBuilder.Append("  and");
                sqlTotalBuilder.Append(" and");
            }
            sqlBuilder.Append(" BusinessId is null");
            sqlTotalBuilder.Append(" BusinessId is null");

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Name like {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Name like {" + parameters.Count.ToString() + "}");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EStaff> list = dbContext.Database.SqlQuery<Entity.EStaff>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<Entity.EStaff>(start, limit, total, list);
            }
        }

        public PagedResult<Entity.EServiceCategory> QueryServiceCategoryPagedList(Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_ServiceCategory ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_ServiceCategory ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                sqlTotalBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    sqlTotalBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    parameters.Add(stationId.Value);
                }
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EServiceCategory> list = dbContext.Database.SqlQuery<Entity.EServiceCategory>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<Entity.EServiceCategory>(start, limit, total, list);
            }
        }

        public PagedResult<View.VServiceItem> QueryServiceItemPagedList(string filter, Guid? categoryId, Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                SqlParameter pFilter = new SqlParameter("@filter", System.Data.SqlDbType.VarChar, 64);
                if (!string.IsNullOrEmpty(filter))
                {
                    pFilter.Value = filter;
                }
                else
                {
                    pFilter.Value = DBNull.Value;
                }
                SqlParameter pCategoryId = new SqlParameter("@categoryId", System.Data.SqlDbType.UniqueIdentifier);
                if (categoryId != null)
                {
                    pCategoryId.Value = categoryId.Value;
                }
                else
                {
                    pCategoryId.Value = DBNull.Value;
                }
                SqlParameter pBusinessId = new SqlParameter("@businessId", System.Data.SqlDbType.UniqueIdentifier);
                if (businessId != null)
                {
                    pBusinessId.Value = businessId.Value;
                }
                else
                {
                    pBusinessId.Value = DBNull.Value;
                }
                SqlParameter pStationId = new SqlParameter("@stationId", System.Data.SqlDbType.UniqueIdentifier);
                if (stationId != null)
                {
                    pStationId.Value = stationId.Value;
                }
                else
                {
                    pStationId.Value = DBNull.Value;
                }
                SqlParameter pStatus = new SqlParameter("@status", System.Data.SqlDbType.Int);
                if (status != Constant.CommonStatus.ALL)
                {
                    pStatus.Value = status;
                }
                else
                {
                    pStatus.Value = DBNull.Value;
                }
                SqlParameter pStart = new SqlParameter("@start", start);
                SqlParameter pLimit = new SqlParameter("@limit", limit);
                SqlParameter pTotal = new SqlParameter("@total", System.Data.SqlDbType.Int);
                pTotal.Direction = System.Data.ParameterDirection.Output;

                try
                {
                    List<View.VServiceItem> list = dbContext.Database.SqlQuery<View.VServiceItem>("[ASMS_SP_QueryServiceItemPagedList] @filter, @categoryId,@businessId,@stationId,@status,@start,@limit,@total out", pFilter, pCategoryId, pBusinessId, pStationId, pStatus, pStart, pLimit, pTotal).ToList();
                    int total = Convert.ToInt32(pTotal.Value);
                    return new PagedResult<View.VServiceItem>(start, limit, total, list);
                }
                catch (Exception ex)
                {
                    return new PagedResult<View.VServiceItem>(1, ex.ToString());
                }
            }
        }

        public PagedResult<Entity.EPayItem> QueryPayItemPagedList(Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_PayItem ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_PayItem ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                sqlTotalBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    sqlTotalBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    parameters.Add(stationId.Value);
                }
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EPayItem> list = dbContext.Database.SqlQuery<Entity.EPayItem>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<Entity.EPayItem>(start, limit, total, list);
            }
        }

        public PagedResult<View.VMaterial> QueryMaterialPagedList(string filter, Guid? businessId, Guid? stationId, string category, string brand, int status, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                SqlParameter pFilter = new SqlParameter("@filter", System.Data.SqlDbType.VarChar, 64);
                if (!string.IsNullOrEmpty(filter))
                {
                    pFilter.Value = filter;
                }
                else
                {
                    pFilter.Value = DBNull.Value;
                }
                SqlParameter pCategory = new SqlParameter("@category", System.Data.SqlDbType.VarChar, 64);
                if (!string.IsNullOrEmpty(category))
                {
                    pCategory.Value = category;
                }
                else
                {
                    pCategory.Value = DBNull.Value;
                }
                SqlParameter pBrand = new SqlParameter("@brand", System.Data.SqlDbType.VarChar, 64);
                if (!string.IsNullOrEmpty(brand))
                {
                    pBrand.Value = brand;
                }
                else
                {
                    pBrand.Value = DBNull.Value;
                }
                SqlParameter pBusinessId = new SqlParameter("@businessId", System.Data.SqlDbType.UniqueIdentifier);
                if (businessId != null)
                {
                    pBusinessId.Value = businessId.Value;
                }
                else
                {
                    pBusinessId.Value = DBNull.Value;
                }
                SqlParameter pStationId = new SqlParameter("@stationId", System.Data.SqlDbType.UniqueIdentifier);
                if (stationId != null)
                {
                    pStationId.Value = stationId.Value;
                }
                else
                {
                    pStationId.Value = DBNull.Value;
                }
                SqlParameter pStatus = new SqlParameter("@status", System.Data.SqlDbType.Int);
                if (status != Constant.CommonStatus.ALL)
                {
                    pStatus.Value = status;
                }
                else
                {
                    pStatus.Value = DBNull.Value;
                }
                SqlParameter pStart = new SqlParameter("@start", start);
                SqlParameter pLimit = new SqlParameter("@limit", limit);
                SqlParameter pTotal = new SqlParameter("@total", System.Data.SqlDbType.Int);
                pTotal.Direction = System.Data.ParameterDirection.Output;

                try
                {
                    List<View.VMaterial> list = dbContext.Database.SqlQuery<View.VMaterial>("[ASMS_SP_QueryMaterialPagedList] @filter, @businessId,@stationId,@category,  @brand,@status,@start,@limit,@total out", pFilter, pBusinessId, pStationId, pCategory, pBrand, pStatus, pStart, pLimit, pTotal).ToList();
                    int total = Convert.ToInt32(pTotal.Value);
                    return new PagedResult<View.VMaterial>(start, limit, total, list);
                }
                catch (Exception ex)
                {
                    return new PagedResult<View.VMaterial>(1, ex.ToString());
                }
            }
        }

        /// <summary>
        /// 获取商品信息，携带库存数量
        /// </summary>
        /// <param name="businessId"></param>
        /// <param name="stationId"></param>
        /// <param name="materialId"></param>
        /// <returns></returns>
        public ListResult<View.VMaterialWithStock> QueryMaterialWithStockList(Guid businessId, Guid stationId, Guid materialId)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VMaterialWithStock> list = dbContext.Set<View.VMaterialWithStock>().Where(p => p.Id == materialId && p.WarehouseBusinessId == businessId && p.WarehouseStationId == stationId).OrderBy(p => p.StockCount).ToList();
                return new ListResult<View.VMaterialWithStock>(list);
            }
        }
        /// <summary>
        /// 
        /// 获取库存商品列表
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="businessId"></param>
        /// <param name="stationId"></param>
        /// <param name="warehouseId"></param>
        /// <param name="category"></param>
        /// <param name="brand"></param>
        /// <param name="status"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public PagedResult<View.VStockMaterial> QueryStockMaterialPagedList(string filter, Guid? businessId, Guid? stationId, Guid? warehouseId, string category, string brand, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_V_StockMaterial ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_StockMaterial ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" (Name like {" + parameters.Count.ToString() + "} or Code like {" + parameters.Count.ToString() + "})");
                sqlTotalBuilder.Append(" (Name like {" + parameters.Count.ToString() + "} or Code like {" + parameters.Count.ToString() + "})");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (warehouseId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" WarehouseId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" WarehouseId = {" + parameters.Count.ToString() + "}");
                parameters.Add(warehouseId.Value);
            }

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append(" where ");
                        sqlTotalBuilder.Append(" where ");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append(" and ");
                        sqlTotalBuilder.Append(" and ");
                    }
                    sqlBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    parameters.Add(stationId.Value);
                }
            }
            if (!string.IsNullOrEmpty(category))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" Category = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Category = {" + parameters.Count.ToString() + "}");
                parameters.Add(category);
            }
            if (!string.IsNullOrEmpty(brand))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" Brand = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Brand = {" + parameters.Count.ToString() + "}");
                parameters.Add(brand);
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VStockMaterial> list = dbContext.Database.SqlQuery<View.VStockMaterial>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VStockMaterial>(start, limit, total, list);
            }
        }
        private PagedResult<View.VMaterial> ActualQueryMaterialPagedList(string category, string brand, int status, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                if (string.IsNullOrEmpty(category))
                {
                    if (string.IsNullOrEmpty(brand))
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Count();

                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status).Count();

                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                    else
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(brand))
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Category == category).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Category == category).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.Category == category).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.Category == category).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);

                        }
                    }
                    else
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Category == category && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Category == category && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.Category == category && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.Category == category && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                }
            }
        }
        private PagedResult<View.VMaterial> ActualQueryMaterialPagedList(Guid businessId, string category, string brand, int status, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                if (string.IsNullOrEmpty(category))
                {
                    if (string.IsNullOrEmpty(brand))
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId).Count();

                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId).Count();

                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                    else
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(brand))
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.Category == category).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.Category == category).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.Category == category).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.Category == category).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                    else
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.Category == category && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.Category == category && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.Category == category && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.Category == category && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                }
            }
        }
        private PagedResult<View.VMaterial> ActualQueryMaterialPagedList(Guid businessId, Guid stationId, string category, string brand, int status, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                if (string.IsNullOrEmpty(category))
                {
                    if (string.IsNullOrEmpty(brand))
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId).Count();

                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId).Count();

                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                    else
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(brand))
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId && p.Category == category).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId && p.Category == category).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId && p.Category == category).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId && p.Category == category).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                    else
                    {
                        if (status == Constant.CommonStatus.ALL)
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId && p.Category == category && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.BusinessId == businessId && p.StationId == stationId && p.Category == category && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                        else
                        {
                            List<View.VMaterial> list = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId && p.Category == category && p.Brand == brand).OrderBy(p => p.Code).Skip(start).Take(limit).ToList();
                            int total = dbContext.Set<View.VMaterial>().Where(p => p.Status == status && p.BusinessId == businessId && p.StationId == stationId && p.Category == category && p.Brand == brand).Count();
                            return new PagedResult<View.VMaterial>(start, limit, total, list);
                        }
                    }
                }
            }
        }

        #region warehouse

        public PagedResult<View.VWarehouse> QueryWarehousePagedList(string filter, Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by BusinessName, StationName, Code desc) as _RowIndex, * from ASMS_V_Warehouse ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_Warehouse ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append(" where ");
                        sqlTotalBuilder.Append(" where ");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append(" and ");
                        sqlTotalBuilder.Append(" and ");
                    }
                    sqlBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    parameters.Add(stationId.Value);
                }
            }

            if (string.IsNullOrEmpty(filter) == false)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" Name like {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Name like {" + parameters.Count.ToString() + "}");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VWarehouse> list = dbContext.Database.SqlQuery<View.VWarehouse>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VWarehouse>(start, limit, total, list);
            }
        }
        #endregion

        #region stock

        public PagedResult<View.VStock> QueryStockPagedList(string filter, int queryType, Guid? businessId, Guid? stationId, Guid? warehouseId, Guid? materialId, string category, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by MaterialName desc) as _RowIndex, * from asms_v_stock ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from asms_v_stock ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append(" where ");
                        sqlTotalBuilder.Append(" where ");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append(" and ");
                        sqlTotalBuilder.Append(" and ");
                    }
                    sqlBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    parameters.Add(stationId.Value);
                }
            }

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" MaterialName like {0}");
                sqlTotalBuilder.Append(" MaterialName like {0}");
                parameters.Add(string.Format("%{0}%", filter));

            }

            if (warehouseId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" WarehouseId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" WarehouseId = {" + parameters.Count.ToString() + "}");
                parameters.Add(warehouseId.Value);
            }
            if (materialId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" materialId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" materialId = {" + parameters.Count.ToString() + "}");
                parameters.Add(materialId.Value);
            }
            if (!string.IsNullOrEmpty(category))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" MaterialCategory = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" MaterialCategory = {" + parameters.Count.ToString() + "}");
                parameters.Add(category);
            }


            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VStock> list = dbContext.Database.SqlQuery<View.VStock>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VStock>(start, limit, total, list);
            }
        }

        public PagedResult<Data.StockWarning> QueryStockWarningPagedList(Guid? businessId, Guid? stationId, Guid? warehouseId, Guid? materialId, int start, int limit)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                SqlParameter pBusinessId = new SqlParameter("@businessId", System.Data.SqlDbType.UniqueIdentifier);
                if (businessId != null)
                {
                    pBusinessId.Value = businessId.Value;
                }
                else
                {
                    pBusinessId.Value = DBNull.Value;
                }
                SqlParameter pStationId = new SqlParameter("@stationId", System.Data.SqlDbType.UniqueIdentifier);
                if (stationId != null)
                {
                    pStationId.Value = stationId.Value;
                }
                else
                {
                    pStationId.Value = DBNull.Value;
                }
                SqlParameter pWarehouseId = new SqlParameter("@warehouseId", System.Data.SqlDbType.UniqueIdentifier);
                if (warehouseId != null)
                {
                    pWarehouseId.Value = warehouseId.Value;
                }
                else
                {
                    pWarehouseId.Value = DBNull.Value;
                }
                SqlParameter pMaterialId = new SqlParameter("@materialId", System.Data.SqlDbType.UniqueIdentifier);
                if (materialId != null)
                {
                    pMaterialId.Value = materialId.Value;
                }
                else
                {
                    pMaterialId.Value = DBNull.Value;
                }
                SqlParameter pStart = new SqlParameter("@start", start);
                SqlParameter pLimit = new SqlParameter("@limit", limit);
                SqlParameter pTotal = new SqlParameter("@total", System.Data.SqlDbType.Int);
                pTotal.Direction = System.Data.ParameterDirection.Output;

                try
                {
                    List<Data.StockWarning> list = dbContext.Database.SqlQuery<Data.StockWarning>("[ASMS_SP_QueryStockWarningPagedList] @businessId,@stationId,@warehouseId,@materialId,@start,@limit,@total out", pBusinessId, pStationId, pWarehouseId, pMaterialId, pStart, pLimit, pTotal).ToList();
                    int total = Convert.ToInt32(pTotal.Value);
                    return new PagedResult<Data.StockWarning>(start, limit, total, list);
                }
                catch (Exception ex)
                {
                    return new PagedResult<Data.StockWarning>(1, ex.ToString());
                }
            }
        }

        public PagedResult<View.VStockChangedRecord> QueryStockChangedRecordPagedList(Guid? businessId, Guid? stationId, Guid? warehouseId, Guid? materialId, DateTime? beginTime, DateTime? endTime, int type, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by MaterialName desc) as _RowIndex, * from ASMS_V_StockChangedRecord ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_StockChangedRecord ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                    parameters.Add(stationId.Value);
                }
            }

            if (warehouseId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" WarehouseId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" WarehouseId = {" + parameters.Count.ToString() + "}");
                parameters.Add(warehouseId.Value);
            }

            if (materialId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" MaterialId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" MaterialId = {" + parameters.Count.ToString() + "}");
                parameters.Add(materialId.Value);
            }

            if (beginTime != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                parameters.Add(beginTime.Value);
            }

            if (endTime != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CreatedTime <= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CreatedTime <= {" + parameters.Count.ToString() + "}");
                parameters.Add(endTime.Value);
            }

            if (type != Constant.StockChangeTypes.All)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" [Type] = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" [Type] = {" + parameters.Count.ToString() + "}");
                parameters.Add(type);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VStockChangedRecord> list = dbContext.Database.SqlQuery<View.VStockChangedRecord>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VStockChangedRecord>(start, limit, total, list);
            }
        }
        #endregion

        public PagedResult<View.VSupplier> QuerySupplierPagedList(string filter, Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from asms_v_supplier ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from asms_v_supplier ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Name like {0}");
                sqlTotalBuilder.Append(" Name like {0}");
                parameters.Add(string.Format("%{0}%", filter));

            }

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
                    parameters.Add(stationId.Value);
                }
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VSupplier> list = dbContext.Database.SqlQuery<View.VSupplier>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VSupplier>(start, limit, total, list);
            }
        }

        #region maintenanceBill
        public PagedResult<View.VMaintenanceBill> QueryMaintenanceBillPageList(string filter, DateTime? beginTime, DateTime? endTime, Guid? businessId, Guid? stationId, int[] statusArray, int[] serviceItemStatusArray, int[] materialStatusArray, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Number desc) as _RowIndex, * from ASMS_V_MaintenanceBill ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_MaintenanceBill ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (Number like  {" + parameters.Count.ToString() + "} or LPN like  {" + parameters.Count.ToString() + "} or CustomerName like  {" + parameters.Count.ToString() + "} or CustomerTelephone like {" + parameters.Count.ToString() + "}) ");
                sqlTotalBuilder.Append(" (Number like  {" + parameters.Count.ToString() + "} or LPN like  {" + parameters.Count.ToString() + "} or CustomerName like  {" + parameters.Count.ToString() + "} or CustomerTelephone like {" + parameters.Count.ToString() + "}) ");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (beginTime != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CreatedTime >={" + parameters.Count.ToString() + "}");
                parameters.Add(beginTime.Value);
            }

            if (endTime != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CreatedTime <= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CreatedTime <={" + parameters.Count.ToString() + "}");
                parameters.Add(endTime.Value);
            }

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
                    parameters.Add(stationId.Value);
                }
            }

            if (statusArray != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }

                StringBuilder sbStatus = new StringBuilder();
                for (int index = 0; index < statusArray.Length; index++)
                {
                    if (index == 0)
                    {
                        sbStatus.AppendFormat("{0}", statusArray[index]);
                    }
                    else
                    {
                        sbStatus.AppendFormat(",{0}", statusArray[index]);
                    }
                }
                sqlBuilder.AppendFormat(" Status in ({0})", sbStatus);
                sqlTotalBuilder.AppendFormat(" Status in ({0})", sbStatus);

                if (serviceItemStatusArray != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    StringBuilder sbServiceItemStatus = new StringBuilder();
                    for (int index = 0; index < serviceItemStatusArray.Length; index++)
                    {
                        if (index == 0)
                        {
                            sbServiceItemStatus.AppendFormat("{0}", serviceItemStatusArray[index]);
                        }
                        else
                        {
                            sbServiceItemStatus.AppendFormat(",{0}", serviceItemStatusArray[index]);
                        }
                    }

                    sqlBuilder.AppendFormat(" ServiceItemStatus in ({0})", sbServiceItemStatus);
                    sqlTotalBuilder.AppendFormat(" ServiceItemStatus in ({0})", sbServiceItemStatus);
                }

                if (materialStatusArray != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    StringBuilder sbServiceItemStatus = new StringBuilder();
                    for (int index = 0; index < materialStatusArray.Length; index++)
                    {
                        if (index == 0)
                        {
                            sbServiceItemStatus.AppendFormat("{0}", materialStatusArray[index]);
                        }
                        else
                        {
                            sbServiceItemStatus.AppendFormat(",{0}", materialStatusArray[index]);
                        }
                    }

                    sqlBuilder.AppendFormat(" MaterialStatus in ({0})", sbServiceItemStatus);
                    sqlTotalBuilder.AppendFormat(" MaterialStatus in ({0})", sbServiceItemStatus);
                }
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VMaintenanceBill> list = dbContext.Database.SqlQuery<View.VMaintenanceBill>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VMaintenanceBill>(start, limit, total, list);
            }
        }

        #endregion

        #region business and station

        /// <summary>
        /// 获取商家
        /// </summary>
        /// <returns></returns>
        public ListResult<Entity.EBusiness> QueryBusinessList()
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EBusiness> list = dbContext.Set<Entity.EBusiness>().Where(p => p.Status == Constant.CommonStatus.Enabled).OrderBy(p => p.Name).ToList();
                return new ListResult<Entity.EBusiness>(list);
            }
        }

        public PagedResult<View.VBusiness> QueryBusinessPagedList(string filter, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_V_Business ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_Business ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (Name like {" + parameters.Count.ToString() + "} or ShortName like {" + parameters.Count.ToString() + "}) ");
                sqlTotalBuilder.Append(" (Name like {" + parameters.Count.ToString() + "} or ShortName like {" + parameters.Count.ToString() + "}) ");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VBusiness> list = dbContext.Database.SqlQuery<View.VBusiness>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VBusiness>(start, limit, total, list);
            }
        }

        public ListResult<View.VStation> QueryStationList(Guid? businessId, Guid? stationId)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                if (businessId == null)
                {
                    List<View.VStation> list = dbContext.Set<View.VStation>().Where(p => p.Status == Constant.CommonStatus.Enabled).OrderBy(p => p.Name).ToList();
                    return new ListResult<View.VStation>(list);
                }
                else
                {
                    if (stationId == null)
                    {
                        List<View.VStation> list = dbContext.Set<View.VStation>().Where(p => p.Status == Constant.CommonStatus.Enabled && p.BusinessId == businessId.Value).OrderBy(p => p.Name).ToList();
                        return new ListResult<View.VStation>(list);
                    }
                    else
                    {
                        List<View.VStation> list = dbContext.Set<View.VStation>().Where(p => p.Status == Constant.CommonStatus.Enabled && p.BusinessId == businessId.Value && (p.Id == stationId.Value || p.ParentStationId == stationId.Value)).OrderBy(p => p.Name).ToList();
                        return new ListResult<View.VStation>(list);
                    }
                }
            }
        }

        public PagedResult<View.VStation> QueryStationPagedList(string filter, Guid? businessId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_V_Station ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_Station ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append(" where ");
                    sqlTotalBuilder.Append(" where ");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append(" and ");
                    sqlTotalBuilder.Append(" and ");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);
            }

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (Name like {" + parameters.Count.ToString() + "} or BusinessName like {" + parameters.Count.ToString() + "}) ");
                sqlTotalBuilder.Append(" (Name like {" + parameters.Count.ToString() + "} or BusinessName like {" + parameters.Count.ToString() + "}) ");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VStation> list = dbContext.Database.SqlQuery<View.VStation>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VStation>(start, limit, total, list);
            }
        }

        #endregion

        #region PackagePurchaseBill
        public PagedResult<Entity.EPackage> QueryPackagePagedList(string filter, Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from asms_package ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from asms_package ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (Code like {" + parameters.Count.ToString() + "} or Name like {" + parameters.Count.ToString() + "}) ");
                sqlTotalBuilder.Append(" (Code like {" + parameters.Count.ToString() + "} or Name like {" + parameters.Count.ToString() + "}) ");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                sqlTotalBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    sqlTotalBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    parameters.Add(stationId.Value);
                }
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EPackage> list = dbContext.Database.SqlQuery<Entity.EPackage>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<Entity.EPackage>(start, limit, total, list);
            }
        }

        public ListResult<Entity.EPackageItem> QueryPackageItemList(Guid packageId)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EPackageItem> list = dbContext.Set<Entity.EPackageItem>().Where(p => p.PackageId == packageId).OrderBy(p => p.Name).ToList();
                return new ListResult<Entity.EPackageItem>(list);
            }
        }

        public ListResult<Entity.ESalesPrice> QuerySalePriceList(Guid targetId, int targetType)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.ESalesPrice> list = dbContext.Set<Entity.ESalesPrice>().Where(p => p.TargetId == targetId && p.TargetType == targetType && p.Status == Constant.CommonStatus.Enabled).ToList();
                return new ListResult<Entity.ESalesPrice>(list);
            }
        }

        /// <summary>
        /// 获取套餐购买单列表
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="businessId"></param>
        /// <param name="stationId"></param>
        /// <param name="customerId"></param>
        /// <param name="salesManagerId"></param>
        /// <param name="status"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public PagedResult<View.VPackagePurchaseBill> QueryPackagePurchaseBill(string filter, Guid? businessId, Guid? stationId, Guid? customerId, Guid? salesManagerId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by PackageName desc) as _RowIndex, * from ASMS_V_PackagePurchaseBill ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_PackagePurchaseBill ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (!string.IsNullOrEmpty(filter))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (PackageName like {" + parameters.Count.ToString() + "}  or PackageCode like {" + parameters.Count.ToString() + "} or CustomerName like {" + parameters.Count.ToString() + "}) ");
                sqlTotalBuilder.Append(" (PackageName like {" + parameters.Count.ToString() + "} or PackageCode like {" + parameters.Count.ToString() + "} or  CustomerName like {" + parameters.Count.ToString() + "}) ");
                parameters.Add(string.Format("%{0}%", filter));
            }

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                sqlTotalBuilder.Append(" (BusinessId = {" + parameters.Count.ToString() + "} or BusinessId is null)");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    sqlTotalBuilder.Append(" (Stationid = {" + parameters.Count.ToString() + "} or StationId is null)");
                    parameters.Add(stationId.Value);
                }
            }

            if (customerId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CustomerId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CustomerId = {" + parameters.Count.ToString() + "}");
                parameters.Add(customerId.Value);
            }


            if (salesManagerId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" SaleManagerId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" SaleManagerId = {" + parameters.Count.ToString() + "}");
                parameters.Add(salesManagerId.Value);
            }

            if (status != Constant.PackagePurchaseBillStatus.All)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VPackagePurchaseBill> list = dbContext.Database.SqlQuery<View.VPackagePurchaseBill>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VPackagePurchaseBill>(start, limit, total, list);
            }
        }

        public PagedResult<View.VPackageInstanceItemChangedRecord> QueryPackageInstanceItemChangedRecordPagedList(Guid? customerId, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by CreatedTime desc) as _RowIndex, * from ASMS_V_PackageInstanceItemChangedRecord ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_PackageInstanceItemChangedRecord ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (customerId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CustomerId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CustomerId = {" + parameters.Count.ToString() + "}");
                parameters.Add(customerId.Value);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VPackageInstanceItemChangedRecord> list = dbContext.Database.SqlQuery<View.VPackageInstanceItemChangedRecord>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VPackageInstanceItemChangedRecord>(start, limit, total, list);
            }
        }

        /// <summary>
        /// 获取用户的套餐列表
        /// </summary>
        /// <param name="businessId"></param>
        /// <param name="stationId"></param>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public ListResult<Data.PackageInstanceDetail> QueryEnabledPackageInstanceList(Guid businessId, Guid stationId, Guid customerId)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VPackageInstance> list = dbContext.Database.SqlQuery<View.VPackageInstance>("exec [ASMS_SP_QueryEnabledPackageInstanceList] @businessId={0},@stationId={1},@customerId={2}", businessId, stationId, customerId).ToList();

                List<Data.PackageInstanceDetail> dataList = new List<Data.PackageInstanceDetail>();
                for (int index = 0; index < list.Count; index++)
                {
                    View.VPackageInstance instance = list[index];
                    Data.PackageInstanceDetail data = new Data.PackageInstanceDetail();
                    data.Instance = instance;
                    data.Items = dbContext.Set<View.VPackageInstanceItem>().Where(p => p.PackageInstanceId == instance.Id && p.Value > 0).ToList();
                    dataList.Add(data);
                }

                return new ListResult<Data.PackageInstanceDetail>(dataList);
            }
        }
        #endregion

        #region recharge
        public PagedResult<Entity.ERechargeItem> QueryRechargeItemPagedList(Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by Name desc) as _RowIndex, * from ASMS_RechargeItem ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_RechargeItem ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" (BusinessId is null or BusinessId = {" + parameters.Count.ToString() + "})");
                sqlTotalBuilder.Append(" (BusinessId is null or BusinessId = {" + parameters.Count.ToString() + "})");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" (StationId is null or Stationid = {" + parameters.Count.ToString() + "})");
                    sqlTotalBuilder.Append(" (StationId is null or Stationid = {" + parameters.Count.ToString() + "})");
                    parameters.Add(stationId.Value);
                }
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.ERechargeItem> list = dbContext.Database.SqlQuery<Entity.ERechargeItem>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<Entity.ERechargeItem>(start, limit, total, list);
            }
        }

        public PagedResult<View.VRechargeBill> QueryRechargeBillPagedList(Guid? customerId, Guid? businessId, Guid? stationId, int status, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by CreatedTime desc) as _RowIndex, * from ASMS_V_RechargeBill ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_RechargeBill ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;

            if (customerId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CustomerId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CustomerId = {" + parameters.Count.ToString() + "}");
                parameters.Add(customerId.Value);
            }

            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);

                if (stationId != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" Stationid = {" + parameters.Count.ToString() + "}");
                    parameters.Add(stationId.Value);
                }
            }

            if (status != Constant.CommonStatus.ALL)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" Status = {" + parameters.Count.ToString() + "}");
                parameters.Add(status);
            }
            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VRechargeBill> list = dbContext.Database.SqlQuery<View.VRechargeBill>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VRechargeBill>(start, limit, total, list);
            }
        }
        #endregion

        #region capital
        public PagedResult<View.VCapitalIncreaseBill> QueryCapitalIncreaseBillPagedList(Guid? businessId, Guid? stationId, DateTime? beginTime, DateTime? endTime, int changedType, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by CreatedTime desc) as _RowIndex, * from ASMS_V_CapitalIncreaseBill ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_CapitalIncreaseBill ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;
            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);
            }

            if (stationId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                parameters.Add(stationId.Value);
            }

            if (beginTime != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                parameters.Add(beginTime.Value);

                if (endTime != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                    parameters.Add(endTime.Value);
                }
            }

            if (changedType != Constant.CapitalChangedType.All)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" ChangedType = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" ChangedType = {" + parameters.Count.ToString() + "}");
                parameters.Add(changedType);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VCapitalIncreaseBill> list = dbContext.Database.SqlQuery<View.VCapitalIncreaseBill>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VCapitalIncreaseBill>(start, limit, total, list);
            }
        }

        public PagedResult<View.VCapitalDecreaseBill> QueryCapitalDecreaseBillPagedList(Guid? businessId, Guid? stationId, DateTime? beginTime, DateTime? endTime, int changedType, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by CreatedTime desc) as _RowIndex, * from ASMS_V_CapitalDecreaseBill ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_CapitalDecreaseBill ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;
            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);
            }

            if (stationId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                parameters.Add(stationId.Value);
            }

            if (beginTime != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                parameters.Add(beginTime.Value);

                if (endTime != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                    parameters.Add(endTime.Value);
                }
            }

            if (changedType != Constant.CapitalChangedType.All)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" ChangedType = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" ChangedType = {" + parameters.Count.ToString() + "}");
                parameters.Add(changedType);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VCapitalDecreaseBill> list = dbContext.Database.SqlQuery<View.VCapitalDecreaseBill>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VCapitalDecreaseBill>(start, limit, total, list);
            }
        }

        public PagedResult<View.VCapitalChangedRecord> QueryCapitalChangedRecordPagedList(Guid? businessId, Guid? stationId, DateTime? beginTime, DateTime? endTime, int changedType, string payChannel, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder("select Row_Number() over(order by CreatedTime desc) as _RowIndex, * from ASMS_V_CapitalChangedRecord ");
            StringBuilder sqlTotalBuilder = new StringBuilder("select count(Id) as [Total] from ASMS_V_CapitalChangedRecord ");
            List<object> parameters = new List<object>();

            bool isFirstCondition = true;
            if (businessId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" BusinessId = {" + parameters.Count.ToString() + "}");
                parameters.Add(businessId.Value);
            }

            if (stationId != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" StationId = {" + parameters.Count.ToString() + "}");
                parameters.Add(stationId.Value);
            }

            if (beginTime != null)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                parameters.Add(beginTime.Value);

                if (endTime != null)
                {
                    if (isFirstCondition)
                    {
                        sqlBuilder.Append("  where");
                        sqlTotalBuilder.Append(" where");
                        isFirstCondition = false;
                    }
                    else
                    {
                        sqlBuilder.Append("  and");
                        sqlTotalBuilder.Append(" and");
                    }
                    sqlBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                    sqlTotalBuilder.Append(" CreatedTime >= {" + parameters.Count.ToString() + "}");
                    parameters.Add(endTime.Value);
                }
            }

            if (changedType != Constant.CapitalChangedType.All)
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" ChangedType = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" ChangedType = {" + parameters.Count.ToString() + "}");
                parameters.Add(changedType);
            }


            if (!string.IsNullOrEmpty(payChannel))
            {
                if (isFirstCondition)
                {
                    sqlBuilder.Append("  where");
                    sqlTotalBuilder.Append(" where");
                    isFirstCondition = false;
                }
                else
                {
                    sqlBuilder.Append("  and");
                    sqlTotalBuilder.Append(" and");
                }
                sqlBuilder.Append(" PayChannel = {" + parameters.Count.ToString() + "}");
                sqlTotalBuilder.Append(" PayChannel = {" + parameters.Count.ToString() + "}");
                parameters.Add(payChannel);
            }

            StringBuilder pagedSqlBuilder = new StringBuilder();
            pagedSqlBuilder.AppendFormat("select T.* from ({0}) as T where T._RowIndex > " + start + " and T._RowIndex <" + (start + limit), sqlBuilder);
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<View.VCapitalChangedRecord> list = dbContext.Database.SqlQuery<View.VCapitalChangedRecord>(pagedSqlBuilder.ToString(), parameters.ToArray()).ToList();
                int total = dbContext.Database.SqlQuery<int>(sqlTotalBuilder.ToString(), parameters.ToArray()).SingleOrDefault();
                return new PagedResult<View.VCapitalChangedRecord>(start, limit, total, list);
            }
        }
        #endregion

        public ListResult<Entity.EBillHeader> QueryBillHeaderList(string billNumber)
        {
            using (ASMSDbContext dbContext = new ASMSDbContext())
            {
                List<Entity.EBillHeader> list = dbContext.Set<Entity.EBillHeader>().Where(p => p.BillNumber == billNumber).ToList();
                return new ListResult<Entity.EBillHeader>(list);
            }
        }
    }
}
